<?php
namespace App\DataBase;


class MysqlAccessor
{
    const OP_GREATER_THAN = '>';
    const OP_LESS_THAN = '<';
    const OP_EQUAL = '=';

    const OP_IN = 'IN';
    const OP_LIKE = 'LIKE';
    const OP_EQ = '=';
    const OP_NE = '!=';
    const OP_GT = '>';
    const OP_GE = '>=';
    const OP_LT = '<';
    const OP_LE = '<=';


    /**
     * @var bool 是否自动提交，启动事务的时候用
     */
    private static $autoCommit = true;
    public static $last_sql = null;

    public static $connections = [];
    /**
     * @var PDO
     */
    public $connection = null;

    public $read_only = false;
    public $isForceMaster = false;

    private $sql;

    private $mapping;

    /**
     * @var string 表名
     */
    private $_tableName = '';
    /**
     * @var string 数据库配置节点
     */
    private $_dataBaseConnectConfigName = '';
    /**
     * @var array 过滤条件字段名称
     */
    private $filter_names = array();
    /**
     * @var array 过滤条件的逻辑运算符
     */
    private $_filterOperators = [];
    /**
     * @var array 过滤条件的值
     */
    private $_filterValues = [];
    /**
     * @var bool 是否使用 distinct
     */
    private $distinct = false;
    /**
     * @var array 查询字段
     */
    private $_loadFields = [];
    /**
     * @var array 排序 [column=>sort_type]
     */
    private $sorts = [];
    /**
     * @var array 更新字段的 [column=>value]
     */
    private $set_fields = [];

    /**
     * @var array 生成SQL 时候暂存的预编译值
     */
    private $_PDOValues = [];

    private $limit = null;
    private $offset = null;

    public static function get_last_sql()
    {
        return self::$last_sql;
    }

    /**
     * 获取 Accessor 对象实例
     *
     * @param string $tableName
     * @param string $configName
     * @return MysqlAccessor
     */
    public function __construct($tableName, $configName)
    {
        $this->_tableName = $tableName;
        $this->_dataBaseConnectConfigName = $configName;
    }

    public static function beginTransaction()
    {
        self::$autoCommit = false;
    }

    /**
     * 提交事务
     */
    public static function commit()
    {
        /**
         * @var PDO $connection
         */
        foreach (self::$connections as $connection) {
            $connection->commit();
        }
    }

    /**
     * 回滚事务
     */
    public static function rollBack()
    {
        /**
         * @var PDO $connection
         */
        foreach (self::$connections as $connection) {
            $connection->rollBack();
        }
    }

    /**
     * 强制使用 主库
     *
     * @return $this
     */
    public function forceMaster()
    {
        $this->isForceMaster = true;
        return $this;
    }

    public function execute($sql, $values, $read_only = true, $fetch_model = PDO::FETCH_ASSOC)
    {
        $this->read_only = $read_only;
        $this->connection = $this->getPDOInstance($this->_dataBaseConnectConfigName);
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($values);
        if ($read_only) {
            return $stmt->fetchAll($fetch_model);
        }
        return $stmt->rowCount();
    }


    public function filter($name, $value, $operator = self::OP_EQUAL)
    {
        if (is_array($value)) {
            $operator = self::OP_IN;

            if (empty($value)) {
                throw new \Exception('values can not be empty array');
            }
        }

        return $this->filterWithOperator($name, $operator, $value);
    }

    /**
     * 添加where条件
     *
     * @param string $name 字段名
     * @param string $op 操作符
     * @param mixed $value 字段值
     *
     * @return $this
     */
    public function filterWithOperator($name, $op, $value)
    {
        $this->filter_names[] = $name;
        $this->_filterOperators[] = $op;
        $this->_filterValues[] = $value;

        return $this;
    }

    public function distinct()
    {
        $this->distinct = true;
        return $this;
    }

    public function loadField($fields)
    {
        if (!is_array($fields)) {
            $fields = [$fields];
        }

        $this->_loadFields = array_merge($this->_loadFields, $fields);

        return $this;
    }

    public function sort($name, $type = 'DESC')
    {
        $this->sorts[$name] = $type;
        return $this;
    }

    /**
     * LIMIT 子句
     *
     * @param $limit
     * @param null|int $offset
     * @return $this
     */
    public function limit($limit, $offset = null)
    {
        $this->limit = $limit;
        if (!is_null($offset)) {
            $this->offset = intval($offset);
        }
        return $this;
    }

    /**
     * 在update/insert的时候，添加数据
     * @param string $name 数据库字段名称
     * @param mixed $value
     * @return $this
     */
    public function setField($name, $value)
    {
        $this->set_fields[$name] = $value;
        return $this;
    }

    /**
     * @return int
     */
    public function update()
    {
        $this->sql = "UPDATE `{$this->_tableName}` ";
        $this->appendUpdateClause();
        $this->appendWhereClause();
        return $this->executeWrite();
    }

    /**
     * @param string $type
     * @return int
     */
    public function insert($type = 'INSERT')
    {
        $this->sql = "{$type} into `{$this->_tableName}` ";
        $this->appendInsertClause();
        return $this->executeWrite();
    }

    /**
     * 返回最后插入行的ID
     *
     * @return string
     */
    public function lastInsertId()
    {
        return $this->connection->lastInsertId();
    }

    public function delete()
    {
        $this->sql = sprintf('DELETE FROM `%s` ', $this->_tableName);
        $this->appendWhereClause();
        $this->appendLimitClause();
        return $this->executeWrite();
    }

    public function count()
    {
        $this->sql = sprintf('SELECT COUNT(1) FROM `%s` ', $this->_tableName);
        $this->appendWhereClause();
        return $this->executeRead()->fetchColumn();
    }

    /**
     * @param string $field Model对象属性名（不推荐使用数据库字段名）
     *
     * @return string
     * @see aggregate()
     */
    public function sum($field)
    {
        return $this->aggregate('sum', $field);
    }

    /**
     * @param string $column
     * @return string
     * @see aggregate()
     */
    public function max($column)
    {
        return $this->aggregate('max', $column);
    }

    /**
     * @param string $column
     *
     * @return string
     * @see aggregate()
     */
    public function min($column)
    {
        return $this->aggregate('min', $column);
    }

    /**
     * @param string $func 聚合函数
     * @param string $column Model对象属性名（不推荐使用数据库字段名）
     *
     * @return string
     * @see aggregate()
     */
    private function aggregate($func, $column)
    {
        $this->sql = sprintf('SELECT %s(%s) FROM `%s `', $func, $column, $this->_tableName);
        $this->appendWhereClause();
        return $this->executeRead()->fetchColumn();
    }

    /**
     * 执行数据库查询
     *
     * @return array
     */
    public function executeQuery()
    {
        $this->read_only = true;

        if (count($this->_loadFields)) {
            $_fields = [];
            $mapping = $this->mapping['columns'];
            foreach ($this->_loadFields as $field) {
                if (isset($mapping[$field])) {
                    $_fields[] = sprintf('`%s`', $mapping[$field]);
                }
            }
            $fields = implode(', ', $_fields);
        } else {
            $fields = '*';
        }

        if ($this->distinct) {
            $fields = 'DISTINCT ' . $fields;
        }

        $this->sql = "select {$fields} from `{$this->_tableName}` ";
        $this->appendWhereClause();
        $this->appendOrderClause();
        $this->appendLimitClause();
        return $this->executeRead()->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 添加Where子句到SQL
     */
    protected function appendWhereClause()
    {
        if ((!count($this->filter_names) || !count($this->_filterOperators) || !count($this->_filterValues))) {
            return;
        }
        $this->sql .= 'WHERE ';
        $tmp = [];
        $property2column = $this->mapping['columns'];
        foreach ($this->filter_names as $k => $name) {
            $columnName = $property2column[$name];
            if (is_array($this->_filterValues[$k])) {
                $valueString = implode(', ', array_fill(0, count($this->_filterValues[$k]), '?'));
                $tmp[] = "`{$columnName}` {$this->_filterOperators[$k]} ({$valueString})";
                $this->_PDOValues = array_merge($this->_PDOValues, $this->_filterValues[$k]);
            } else {
                $tmp[] = "`$columnName` {$this->_filterOperators[$k]} ?";
                $this->_PDOValues[] = $this->_filterValues[$k];
            }
        }

        $this->sql .= implode(' AND ', $tmp) . ' ';
    }

    protected function appendOrderClause()
    {
        if (count($this->sorts)) {
            return;
        }
        $this->sql .= 'ORDER BY ';
        $tmp = [];
        $property2column = $this->mapping['columns'];
        foreach ($this->sorts as $propertyName => $sortType) {
            $tmp[] = "`{$property2column[$propertyName]}` {$sortType}";
        }
        $this->sql .= implode(', ', $tmp) . ' ';
    }

    protected function appendLimitClause()
    {
        if (is_null($this->limit)) {
            return;
        }
        if (is_null($this->offset)) {
            $this->sql .= "LIMIT {$this->limit}";
        } else {
            $this->sql .= "LIMIT {$this->offset},{$this->limit}";
        }
    }

    protected function appendUpdateClause()
    {
        $this->sql .= 'SET ';
        $tmp = [];
        $property2column = $this->mapping['columns'];
        foreach ($this->set_fields as $propertyName => $v) {
            $tmp[] = "`{$property2column[$propertyName]}` = ?";
        }
        $this->sql .= implode(', ', $tmp) . ' ';
        $this->_PDOValues = array_merge($this->_PDOValues, array_values($this->set_fields));
    }

    protected function appendInsertClause()
    {
        $columns = [];
        $values = [];
        $property2column = $this->mapping['columns'];
        foreach ($this->set_fields as $k => $v) {
            $columns[] = $property2column[$k];
            $values[] = '?';
        }
        $this->sql .= sprintf('(`%s`) VALUES (%s) ', implode('`, `', $columns), implode(', ', $values));
        $this->_PDOValues = array_merge($this->_PDOValues, array_values($this->set_fields));
    }

    protected function getPrimaryKeyName()
    {
        return $this->mapping['key'];
    }

    protected function executeInternal()
    {
        $this->connection = $this->getPDOInstance($this->_dataBaseConnectConfigName);
        $statement = $this->connection->prepare($this->sql);
        self::$last_sql = $this->sql;
        $statement->execute($this->_PDOValues);
        return $statement;
    }

    /**
     * 获取PDO对象实例
     *
     * @param $configName
     * @return PDO
     */
    protected function getPDOInstance($configName)
    {
        if (!isset(self::$connections[$configName])) {
            $PDOInstance = Factory::getInstance()->getPDO($configName);
            if (self::$autoCommit === false) {
                $PDOInstance->beginTransaction();
            }
            self::$connections[$configName] = $PDOInstance;
        }
        return self::$connections[$configName];
    }

    /**
     * 执行数据库写操作，返回受影响的记录数
     *
     * @return int
     */
    protected function executeWrite()
    {
        return $this->executeInternal()->rowCount();
    }

    /**
     * 执行数据库读操作
     *
     * @return PDOStatement
     */
    protected function executeRead()
    {
        return $this->executeInternal();
    }
}